package dao.impl;

import java.io.PrintWriter;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import model.Booking;
import util.DBController;
import dao.intf.BookingDao;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class BookingDaoImpl implements BookingDao {

	public List<Booking> getBookingList() {
		List<Booking> bookingList = new ArrayList<Booking>();
		
		
		
		ResultSet rs = DBController.retrieve("Booking", null, null);
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					Booking booking = new Booking();
					booking.setId(rs.getString("ID"));
					booking.setStart(rs.getDate("Start"));
					booking.setEnd(rs.getDate("End"));
					booking.setDuration(rs.getInt("Duration"));
					booking.setAdults(rs.getInt("Adults"));
					booking.setChildren(rs.getInt("Children"));
					booking.setCreatedBy(rs.getString("CreatedBy"));
					booking.setCreateDate(rs.getDate("CreateDate"));
					booking.setUpdatedBy(rs.getString("UpdatedBy"));
					booking.setUpdateDate(rs.getDate("UpdateDate"));
					booking.setUserId(rs.getString("User_ID"));
					booking.setTotalPaid(rs.getDouble("TotalPaid"));
					
					bookingList.add(booking);
				}
			}
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getBookingList() of BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return bookingList;
	}

	public Booking getBookingById(String id) {
		Booking booking = new Booking();
		
		ResultSet rs = DBController.retrieve("Booking", null, "ID='"+id+"'");
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					booking.setId(rs.getString("ID"));
					booking.setStart(rs.getDate("Start"));
					booking.setEnd(rs.getDate("End"));
					booking.setDuration(rs.getInt("Duration"));
					booking.setAdults(rs.getInt("Adults"));
					booking.setChildren(rs.getInt("Children"));
					booking.setCreatedBy(rs.getString("CreatedBy"));
					booking.setCreateDate(rs.getDate("CreateDate"));
					booking.setUpdatedBy(rs.getString("UpdatedBy"));
					booking.setUpdateDate(rs.getDate("UpdateDate"));
					booking.setUserId(rs.getString("User_ID"));
					booking.setTotalPaid(rs.getDouble("TotalPaid"));
				}
			}
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getBookingById() of BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return booking;
	}

	public List<Booking> getBookingItems(String email , String bookingno) {
		List<Booking> bookingList = new ArrayList<Booking>();
		
		String sql  = "SELECT * FROM booking INNER JOIN user ON booking.User_ID = user.ID";
		 
		String condition = "" ;
		if (bookingno != "" )
			condition = " booking.id = '" + bookingno+ "'";
		if (bookingno != ""  && email != "" ) 
			condition = condition + " AND ";
		if(email != "")
			condition = condition + " user.Email = '"+email+"'";

		sql = sql + " WHERE " + condition;
		System.out.println("sql getBookingItems : "+ sql);
		ResultSet rs = DBController.retrieve(sql);
		
		
		try {
			if( rs != null ) {
				
				while( rs.next() ) {
					
					Booking booking = new Booking();
					booking.setId(rs.getString("ID"));
					booking.setStart(rs.getDate("Start"));
					booking.setEnd(rs.getDate("End"));
					booking.setDuration(rs.getInt("Duration"));
					booking.setAdults(rs.getInt("Adults"));
					booking.setChildren(rs.getInt("Children"));
					booking.setCreatedBy(rs.getString("CreatedBy"));
					booking.setCreateDate(rs.getDate("CreateDate"));
					booking.setUpdatedBy(rs.getString("UpdatedBy"));
					booking.setUpdateDate(rs.getDate("UpdateDate"));
					booking.setUserId(rs.getString("User_ID"));
					booking.setTotalPaid(rs.getDouble("TotalPaid"));
					
					bookingList.add(booking);
				}
			}
			//System.out.println("size "+bookingList.size());
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at method getBookingItems() of BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return bookingList;
	}

	public int saveBooking(Booking booking) {
		int recordCount=0;
		try {
			if(booking!=null) {
				String queryStr	 = " INSERT INTO Booking ";
						queryStr+= " (Id,Start,End,Duration,Adults,Children,CreateDate,CreatedBy,UpdateDate,UpdatedBy,User_ID,TotalPaid,BookingStatus)";
						queryStr+= " VALUES ( ";
						queryStr+= "'"+booking.getId()+"','"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getStart())+"','"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getEnd())+"',\n";
						queryStr+= booking.getDuration()+","+booking.getAdults()+","+booking.getChildren()+",'"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getCreateDate())+"',\n";
					    queryStr+= "'"+booking.getCreatedBy()+"',"+"'"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getUpdateDate())+"',"+"'"+booking.getUpdatedBy()+"',\n";
					    queryStr+= "'"+booking.getUserId()+"',"+booking.getTotalPaid()+",'"+booking.getBookingStatus()+"')\n";
				
					    recordCount=DBController.save(queryStr);
					    
					    
				}
			
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at saveBooking BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return recordCount;
	}
	public int updateBooking(Booking booking)
	{
		int recordCount=0;
		try {
			if(booking!=null) {
				String queryStr	 = " UPDATE Booking ";
						queryStr+= " SET Start='"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getStart())+"',";
						queryStr+= " End='"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getEnd())+"',";
						queryStr+= " Duration="+booking.getDuration()+",";
						queryStr+= " Adults="+booking.getAdults()+",";
						queryStr+= " Children="+booking.getChildren()+",";
						queryStr+= " CreateDate='"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getCreateDate())+"',";
						queryStr+= " CreatedBy='"+booking.getCreatedBy()+"',";
						queryStr+= " UpdateDate='"+new SimpleDateFormat("yyyy-MM-dd").format(booking.getUpdateDate())+"',";
						queryStr+= " UpdatedBy='"+booking.getUpdatedBy()+"',";
						queryStr+= " User_ID='"+booking.getUserId()+"',";
						queryStr+= " TotalPaid="+booking.getTotalPaid()+",";
						queryStr+= " BookingStatus='"+booking.getBookingStatus()+"'";
						queryStr+= " WHERE Id='"+booking.getId()+"'";
						
						recordCount=DBController.save(queryStr);
				}
			
			DBController.disconnect();
		} catch (Exception e) {
			System.out.println("Error at saveBooking BookingDaoImpl.");
			e.printStackTrace();
		}
		
		return recordCount;
	}
	public String getMaxbookingId()
	{
		String number="";
		String queryString="SELECT max(replace(id,Right(id,21),''))as maxnum from booking";

		ResultSet rs = DBController.retrieve(queryString);
		
		try {
			if( rs != null ) {
				while( rs.next() ) {
					number=rs.getString("maxnum");
				}
			}
			else
				number="0";
		}catch(Exception ex){}
				
		return number;
		
	}
}
